﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class ReportComissionObject
    {
        public const string sql = @"
SELECT DictionaryPartners.Name as Партнер
	,report.AmountCommission as Расчитано
	,report.Summa as Выплачено
	,(report.AmountCommission - report.Summa) as Сальдо
	,DictionaryProject.Name as Проект
FROM	
(
	SELECT Work2.Partner as Partner
		,COALESCE(Work2.AmountCommission,0) as AmountCommission
		,COALESCE(Transactions1.Summa,0) as Summa
		,Work2.Project as Project
	FROM
		(SELECT Work1.Partner as Partner
		       ,sum(COALESCE(Work1.AmountCommission,0)) as AmountCommission
			   ,Work1.Project as Project
			FROM 
				(	SELECT SupplierCommission as Partner
					      ,sum(COALESCE(Work.PriceCommissionPurchase,0) * COALESCE([Work].[CountWork],0)) as AmountCommission
					      ,Work.Project
					FROM   Work
					WHERE  Work.SupplierCommission is not null
					GROUP BY Work.SupplierCommission, Work.Project
				UNION ALL
					SELECT CustomerCommission as Partner
					      ,sum(COALESCE(Work.PriceCommissionSales,0) * COALESCE([Work].[CountWork],0)) as AmountCommission
					      ,Work.Project as Project
					FROM   Work
					WHERE  Work.CustomerCommission is not null
					GROUP BY Work.CustomerCommission, Work.Project
				) as Work1
			GROUP BY Work1.Partner, Work1.Project
		) as Work2
		LEFT JOIN
			(SELECT Transactions.Partner
			      ,sum(COALESCE(Transactions.Summa,0)) as Summa
			      ,Transactions.Project as Project
			 FROM Transactions
			 WHERE Transactions.Partner is not null and 
			 	   Transactions.Category in 
			 	   (select Value from Settings where Code Like 'code_cat_racx_komisiya%')
			 GROUP BY Transactions.Partner, Transactions.Project) as Transactions1
		ON Work2.Partner = Transactions1.Partner and Work2.Project = Transactions1.Project
		
UNION ALL
		
		SELECT Transactions1.Partner as Partner
			,COALESCE(Work2.AmountCommission,0) as AmountCommission
			,COALESCE(Transactions1.Summa,0) as Summa
			,Transactions1.Project as Project
		FROM
		(
		SELECT Transactions.Partner
			,sum(COALESCE(Transactions.Summa,0)) as Summa
			,Transactions.Project
		FROM Transactions
		WHERE Transactions.Partner is not null and Transactions.Category in 
			(select Value from Settings where Code Like 'code_cat_racx_komisiya%')
		GROUP BY Transactions.Partner, Transactions.Project
		) as Transactions1
		
		LEFT JOIN

			(
			SELECT Work1.Partner as Partner
				,sum(COALESCE(Work1.AmountCommission,0)) as AmountCommission
				,Work1.Project as Project
				FROM 
					(	SELECT SupplierCommission as Partner
						      ,sum(COALESCE(Work.PriceCommissionPurchase,0) * COALESCE([Work].[CountWork],0)) as AmountCommission
						      ,Work.Project as Project
						FROM   Work
						WHERE  Work.SupplierCommission is not null
						GROUP BY Work.SupplierCommission, Work.Project
					UNION ALL
						SELECT CustomerCommission as Partner
						      ,sum(COALESCE(Work.PriceCommissionSales,0) * COALESCE([Work].[CountWork],0)) as AmountCommission
						      ,Work.Project as Project
						FROM   Work
						WHERE  Work.CustomerCommission is not null
						GROUP BY Work.CustomerCommission, Work.Project
					) as Work1
				GROUP BY Work1.Partner, Work1.Project
			) as Work2
		ON Transactions1.Partner = Work2.Partner and Transactions1.Project = Work2.Project
		WHERE Work2.Partner is null and Work2.AmountCommission is null and Work2.Project is null
) as report
LEFT JOIN DictionaryPartners ON DictionaryPartners.ID = Report.Partner
LEFT JOIN DictionaryProject ON DictionaryProject.ID = Report.Project
ORDER BY Report.Partner, Report.Project
";

        public string Партнер { get; set; }
        public decimal? Расчитано { get; set; }
        public decimal? Выплачено { get; set; }
        public decimal? Сальдо { get; set; }
        public string Проект { get; set; }
    }
}
